﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>SQLXML 数据类型示例</title>
    
    <link rel="stylesheet" type="text/css" href="../local/Classic.css">
      
    </link>
    
    <script src="../local/script.js">
      
    </script><script src="../local/script_main.js">&amp;nbsp;</script>
  </head>
  <body>
    <!--Topic built:04/01/2010 05:03:28-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="headerBold">SQLXML 数据类型示例</span>
          </td>
          <td align="right">
            
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      
        
        
    <font color="DarkGray">
      
    </font>
    <p />
    
    <p />
  
        <div id="introductionSection" class="section">
    <p>此 Microsoft SQL Server JDBC Driver 示例应用程序说明如何在关系数据库中存储 XML 数据，如何从数据库中检索 XML<b> </b>数据，以及如何使用 <b>SQLXML</b> Java 数据类型分析 XML<b> </b>数据。</p>
    <p>本部分中的代码示例使用 Simple API for XML (SAX) 分析器。SAX 是一种公开制定的标准，用于对 XML 文档进行基于事件的分析。它还提供了一个用于处理 XML 数据的应用程序编程接口。请注意，应用程序也可以使用其他任何 XML 分析器，例如，文档对象模型 (DOM) 或 Streaming API for XML (StAX) 等。</p>
    <p>文档对象模型 (DOM) 提供了 XML 文档、碎片、节点或节点集的编程表示形式。它还提供了一个用于处理 XML 数据的应用程序编程接口。同样，Streaming API for XML (StAX) 也是一个基于 Java 的 API，用于 XML 的拉式分析。</p>
    <div style="margin: .5em 1.5em .5em 1.5em"><b>重要提示：</b>
      为了使用 SAX 分析器 API，必须从 javax.xml 包导入标准的 SAX 实现。<p />
    </div>
    <p>此示例的代码文件名为 sqlxmlExample.java，该文件可在以下位置找到：</p>
    <p>&lt;<i>安装目录</i>&gt;\sqljdbc_&lt;<i>版本</i>&gt;\&lt;<i>语言</i>&gt;\help\samples\datatypes</p>
  </div><h1 class="heading">要求</h1><div id="requirementsSection" class="section">
    <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">若要运行此示例应用程序，必须将 classpath 设置为包含 sqljdbc4.jar 文件。如果 classpath 缺少 sqljdbc4.jar 项，示例应用程序将引发“找不到类”异常。有关如何设置 classpath 的详细信息，请参阅<a href="6faaf05b-8b70-4ed2-9b44-eee5897f1cd0.htm">使用 JDBC 驱动程序</a>。</p>
      <p xmlns="">此外，还需要访问 SQL Server 2005 AdventureWorks 示例数据库才能运行此示例应用程序。</p>
    </content>
  </div><h1 class="heading">示例</h1><div id="codeExampleSection" class="section">
    <description xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <content>
        <p xmlns="">在下面的示例中，示例代码先建立与 SQL Server 2005 AdventureWorks 数据库的连接，然后调用 createSampleTables 方法。</p>
        <p xmlns="">如果测试表 TestTable1 和 TestTable2 存在，createSampleTables 方法会将它们删除。然后该方法会在 TestTable1 中插入两行。</p>
        <p xmlns="">此外，代码示例还包含以下三个方法和另外一个名为 ExampleContentHandler 的类。</p>
        <p xmlns="">ExampleContentHandler 类实现一个自定义内容处理程序，该处理程序定义用于处理分析器事件的方法。</p>
        <p xmlns="">showGetters 方法演示如何使用 SAX、ContentHandler 和 XMLReader 分析 SQLXML 对象中的数据。首先，该代码示例会创建一个自定义内容处理程序的实例，即 ExampleContentHandler。接下来，创建和执行一个 SQL 语句，该语句从 TestTable1 返回一组数据。然后，代码示例获取 SAX 分析器并分析 XML 数据。</p>
        <p xmlns="">showSetters 方法演示如何使用 SAX、ContentHandler 和 ResultSet 设置 <b>xml</b> 列。首先，它使用 Connection 类的 <a href="cf5bfcdd-bc03-4c5b-b554-d8a8a4067e93.htm">createSQLXML</a> 方法创建一个空的 SQLXML 对象。然后，获取内容处理程序的一个实例以便向 SQLXML 对象中写入数据。接下来，代码示例向 TestTable1 中写入数据。最后，示例代码循环访问结果集中包含的数据行，并使用 <a href="a07e0f30-cbc6-4ad7-b931-c30cad382e64.htm">getSQLXML</a> 方法读取 XML 数据。</p>
        <p xmlns="">showTransformer 方法演示如何使用 SAX 和 Transformer 从一个表中获取 XML 数据，然后将该 XML 数据插入另一个表中。首先，从 TestTable1 中检索源 SQLXML 对象。然后，使用 Connection 类的 <a href="cf5bfcdd-bc03-4c5b-b554-d8a8a4067e93.htm">createSQLXML</a> 方法创建一个空的目标 SQLXML 对象。接下来，更新目标 SQLXML 对象，并将 XML 数据写入 TestTable2。最后，示例代码将循环访问结果集中包含的数据行，并使用 <a href="a07e0f30-cbc6-4ad7-b931-c30cad382e64.htm">getSQLXML</a> 方法读取 TestTable2 中的 XML 数据。</p>
      </content>
    </description>
    <div class="sampleCode"><span codeLanguage="other"><pre>import java.sql.*;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;

import javax.xml.transform.sax.SAXSource;
import javax.xml.transform.sax.SAXResult;
import javax.xml.transform.sax.SAXTransformerFactory;

import org.xml.sax.*;

public class sqlxmlExample {

   public static void main(String[] args) {
	   
      // Create a variable for the connection string.
      String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
            "databaseName=AdventureWorks;integratedSecurity=true;";

      // Declare the JDBC objects.
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
      
      try {
         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         con = DriverManager.getConnection(connectionUrl);

         // Create initial sample data.
         createSampleTables(con);
         
         // The showGetters method demonstrates how to parse the data in the 
         // SQLXML object by using the SAX, ContentHandler and XMLReader.  
         showGetters(con);            

         // The showSetters method demonstrates how to set the xml column 
         // by using the SAX, ContentHandler, and ResultSet.
         showSetters(con);   
         
         // The showTransformer method demonstrates how to get an XML data 
         // from one table and insert that XML data to another table 
         // by using the SAX and the Transformer. 
         showTransformer(con);        
      }
      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
      finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {}
         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
         if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }
  
   private static void showGetters(Connection con) {
	   
      try {
          // Create an instance of the custom content handler.   
          ExampleContentHandler myHandler = new ExampleContentHandler();
		   
          // Create and execute an SQL statement that returns a
          // set of data.       
          String SQL = "SELECT * FROM TestTable1";
          Statement stmt = con.createStatement();
          ResultSet rs = stmt.executeQuery(SQL);
      
          rs.next();
        
          SQLXML xmlSource = rs.getSQLXML("Col3");
        
          // Send SAX events to the custom content handler.
          SAXSource sxSource =  xmlSource.getSource(SAXSource.class);
          XMLReader xmlReader = sxSource.getXMLReader();
          xmlReader.setContentHandler(myHandler);
          
	      System.out.println("showGetters method: Parse an XML data in TestTable1 =&gt; ");
          xmlReader.parse(sxSource.getInputSource());
        
      } catch (Exception e) {
          e.printStackTrace();
      }
   }
   
   private static void showSetters(Connection con) {

      try {
         // Create and execute an SQL statement, retrieving an updatable result set.
         String SQL = "SELECT * FROM TestTable1;";
         Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
         ResultSet rs = stmt.executeQuery(SQL);
		   
         // Create an empty SQLXML object.
         SQLXML sqlxml = con.createSQLXML();
 
         // Set the result value from SAX events.
         SAXResult sxResult = sqlxml.setResult(SAXResult.class);
         ContentHandler myHandler = sxResult.getHandler();

         // Set the XML elements and attributes into the result.
         myHandler.startDocument();
         myHandler.startElement(null, "contact", "contact", null);
         myHandler.startElement(null, "name", "name", null);
         myHandler.endElement(null, "name", "name");
         myHandler.startElement(null, "phone", "phone", null);
         myHandler.endElement(null, "phone", "phone");		   
         myHandler.endElement(null, "contact", "contact");
         myHandler.endDocument();

         // Update the data in the result set.
         rs.moveToInsertRow();
         rs.updateString("Col2", "C");
         rs.updateSQLXML("Col3", sqlxml);
         rs.insertRow();
		   
         // Display the data. 
         System.out.println("showSetters method: Display data in TestTable1 =&gt; ");
         while (rs.next()) {
           System.out.println(rs.getString("Col1") + " : " + rs.getString("Col2"));
           SQLXML xml = rs.getSQLXML("Col3");              
           System.out.println("XML column : " + xml.getString());
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   private static void showTransformer(Connection con) {

      try {
          // Create and execute an SQL statement that returns a
          // set of data.       
          String SQL = "SELECT * FROM TestTable1";
          Statement stmt = con.createStatement();
          ResultSet rs = stmt.executeQuery(SQL);
	       
          rs.next();
	        
          // Get the value of the source SQLXML object from the database.
          SQLXML xmlSource = rs.getSQLXML("Col3");
	        
          // Get a Source to read the XML data. 
          SAXSource sxSource =  xmlSource.getSource(SAXSource.class);
	        
         // Create a destination SQLXML object without any data.
         SQLXML xmlDest = con.createSQLXML();

         // Get a Result to write the XML data.
         SAXResult sxResult = xmlDest.setResult(SAXResult.class);
	        
         // Transform the Source to a Result by using the identity transform.
         SAXTransformerFactory stf = (SAXTransformerFactory) TransformerFactory.newInstance();
         Transformer identity = stf.newTransformer();
         identity.transform(sxSource, sxResult);

         // Insert the destination SQLXML object into the database.
         PreparedStatement psmt = 
           con.prepareStatement(
              "INSERT INTO TestTable2" + " (Col2, Col3, Col4, Col5) VALUES (?, ?, ?, ?)");
         psmt.setString(1, "A");
         psmt.setString(2, "Test data");
         psmt.setInt(3, 123);
         psmt.setSQLXML(4, xmlDest);
         psmt.execute();
	        
         // Execute the query and display the data.	
         SQL = "SELECT * FROM TestTable2";
         stmt = con.createStatement();
         rs = stmt.executeQuery(SQL);

         System.out.println("showTransformer method : Display data in TestTable2 =&gt; ");
         while (rs.next()) {
            System.out.println(rs.getString("Col1") + " : " + rs.getString("Col2"));
            System.out.println(rs.getString("Col3") + " : " + rs.getInt("Col4"));
	          
            SQLXML xml = rs.getSQLXML("Col5");              
            System.out.println("XML column : " + xml.getString());
         }
     } catch (Exception e) {
        e.printStackTrace();
     }
   }
   
   private static void createSampleTables(Connection con) {

      try {
	  Statement stmt = con.createStatement();
		  
          // Drop the tables.
	  stmt.executeUpdate("if exists (select * from sys.objects where name = 'TestTable1')" +
                "drop table TestTable1" );

          stmt.executeUpdate("if exists (select * from sys.objects where name = 'TestTable2')" +
                "drop table TestTable2" );		  

          // Create empty tables.
          stmt.execute("CREATE TABLE TestTable1 (Col1 int IDENTITY, Col2 char, Col3 xml)");
          stmt.execute("CREATE TABLE TestTable2 (Col1 int IDENTITY, Col2 char, Col3 varchar(50), Col4 int, Col5 xml)");

          // Insert two rows to the TestTable1.
         String row1 = "&lt;contact&gt;&lt;name&gt;Contact Name 1&lt;/name&gt;&lt;phone&gt;XXX-XXX-XXXX&lt;/phone&gt;&lt;/contact&gt;";
         String row2 = "&lt;contact&gt;&lt;name&gt;Contact Name 2&lt;/name&gt;&lt;phone&gt;YYY-YYY-YYYY&lt;/phone&gt;&lt;/contact&gt;";

         stmt.executeUpdate("insert into TestTable1" + " (Col2, Col3) values('A', '" + row1 +"')");
         stmt.executeUpdate("insert into TestTable1" + " (Col2, Col3) values('B', '" + row2 +"')");
	      
       } catch (Exception e) {
          e.printStackTrace();
       }
   }
}

class ExampleContentHandler implements ContentHandler {

  public void startElement(String namespaceURI, String localName, String qName, Attributes atts) 
  throws SAXException {
	  System.out.println("startElement method: localName =&gt; " + localName);
  }
  public void characters(char[] text, int start, int length) throws SAXException {
  	  System.out.println("characters method");  
  }
  public void endElement(String namespaceURI, String localName, String qName) throws SAXException {
      System.out.println("endElement method: localName =&gt; " + localName);		  
  } 
  public void setDocumentLocator(Locator locator) {
	  System.out.println("setDocumentLocator method");
  }
  public void startDocument() throws SAXException {
	  System.out.println("startDocument method");
  }
  public void endDocument() throws SAXException {
	  System.out.println("endDocument method");
  }
  public void startPrefixMapping(String prefix, String uri) throws SAXException {
	  System.out.println("startPrefixMapping method: prefix =&gt; " + prefix);
  }
  public void endPrefixMapping(String prefix) throws SAXException {
	  System.out.println("endPrefixMapping method: prefix =&gt; " + prefix);
  }
  public void skippedEntity(String name) throws SAXException {
	  System.out.println("skippedEntity method: name =&gt; " + name);
  }  
  public void ignorableWhitespace(char[] text, int start, int length) throws SAXException {
	  System.out.println("ignorableWhiteSpace method");
  }
  public void processingInstruction(String target, String data) throws SAXException {
	  System.out.println("processingInstruction method: target =&gt; " + target);
  }
}
</pre></span></div>
  </div><span id="seeAlsoSpan"><h1 class="heading">请参阅</h1></span><div id="seeAlsoSection" class="section" name="collapseableSection"><a href="b39f44d0-3710-4bc6-880c-35bd8c10a734.htm">使用数据类型 (JDBC)</a><br /><br /></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]-->
      <div id="footer" class="section">
        
		<hr />
		
		<span id="fb" class="feedbackcss">
			
			
		</span>
		
		<a href="9bad553b-9e70-4696-8499-2e35f772a1e0.htm">
			
			© 2010 Microsoft Corporation。保留所有权利。
		</a>
 	
	
      </div>
    </div>
  </body>
</html>